package com.cyz.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cyz.dao.ProductDao;
import com.cyz.pojo.Product;
import com.cyz.pojo.Producttype;
import com.cyz.pojo.User;
import com.cyz.util.DBOperation;
import com.cyz.util.Dbutil;

public class ProductDaoImpl implements ProductDao {
	static Connection Conn=Dbutil.getConnection();
	static PreparedStatement pst;
	/**
	 * ͨ通过ID来获取产品对象
	 * @throws SQLException 
	 */
	public Product getProductById(Integer id) throws SQLException {
		Product product=new Product();
		ResultSet rs = null;
		String sql="select * from product where id=?";
		List<Object> param = new ArrayList<Object>();
		param.add(id);
		rs = DBOperation.query(sql, param);
		while(rs.next())
		{
			product.setId(rs.getInt("id"));
			product.setProductName(rs.getString("productName"));
			product.setPrice(rs.getFloat("price"));
			product.setStock(rs.getInt("stock"));;
			product.setImgAddress(rs.getString("imgAddress"));
			product.setTypeId(rs.getInt("typeId"));
			product.setInfo(rs.getString("info"));
		}
		return product ;
	}
/**
 * 通过类型的名字还获取对应种类的所有产品
 */
	@Override
	public List<Product> getProductByTypeName(String typeName) throws SQLException {
		// TODO Auto-generated method stub
			List<Product> productList=new ArrayList<Product>();
			ResultSet rs = null;
			String sql=	"select * from product where typeId=(select id from productType where typeName=?)";
			List<Object> param = new ArrayList<Object>();
			param.add(typeName);
			rs = DBOperation.query(sql, param); 
			//pst=Conn.prepareStatement(sql);
			 //rs=pst.executeQuery();
			while(rs.next())
			{
				Product product=new Product();
				product.setId(rs.getInt("id"));
				product.setProductName(rs.getString("productName"));
				product.setPrice(rs.getFloat("price"));
				product.setStock(rs.getInt("stock"));;
				product.setImgAddress(rs.getString("imgAddress"));
				product.setTypeId(rs.getInt("typeId"));
				product.setInfo(rs.getString("info"));
				productList.add(product);
			}
		return productList;
	
	}
	/**
	 * 通过产品姓名来获取一个产品对象
	 */
	@Override
	public Product getProductByProductName(String ProductName) {
		// TODO Auto-generated method stub
		Product arr = null;
		ResultSet rs = null;
		try {
	
			String sql = "select * from product where productName =?";
			List<Object> param = new ArrayList<Object>();
			param.add(ProductName);
			rs = DBOperation.query(sql, param);
			
			while (rs.next()) 
			{
				arr=new Product();
				arr.setId(rs.getInt("id"));
				arr.setProductName(rs.getString("productName"));
				arr.setPrice(rs.getFloat("price"));
				arr.setStock(rs.getInt("stock"));
				arr.setTypeId(rs.getInt("typeId"));
				arr.setImgAddress(rs.getString("imgAddress"));
				arr.setInfo(rs.getString("info"));

				
			  }
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
	
        return  arr;
	
	}
	/**
	 * 获取类型名字来新增种类
	 */
	@Override
	public void addType(String typeName) {
		String sql="insert into producttype (typeName) values(?)";
		List<Object> param = new ArrayList<Object>();
		param.add(typeName);
		try {
			DBOperation.update(sql, param);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
	/**
	 * 获取类型名字来删除种类
	 */
	@Override
	public void deleteType(String typeName) {
		String sql="delete from producttype where typeName=?";
		List<Object> param = new ArrayList<Object>();
		param.add(typeName);
		try {
			DBOperation.update(sql, param);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 来对产品的类型的名字进行更改
	 */
	@Override
	public void updateType(String typeName, String newName) {
		String sql="update producttype set typeName=? where typeName=?";
		List<Object> param = new ArrayList<Object>();
		param.add(newName);
		param.add(typeName);
		try {
			DBOperation.update(sql, param);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 通过类型名来查找自己是否存在
	 */
	@Override
	public String getTypeByName(String typeName) {
		String sql="select * from producttype where typeName=?";
		List<Object> param=new ArrayList<Object>();
		param.add(typeName);
		String name=null;

		ResultSet rs=DBOperation.query(sql, param);
		try {
			while(rs.next())
			{
				name=rs.getString("typeName");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return name;
	}
	/**
	 * 通过类型名字来获取类型id
	 */
	@Override
	public Integer getTypeIdByTypeName(String typeName) {
		String sql="select * from producttype where typeName=?";
		List<Object> param=new ArrayList<Object>();
		param.add(typeName);
		Integer id=null;

		ResultSet rs=DBOperation.query(sql, param);
		try {
			while(rs.next())
			{
				id=rs.getInt("id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return id;
	}
	/**
	 * 通过产品名字来删除产品
	 */
	@Override
	public void deleteProduct(String productName) {
		String sql="delete from product where productName=?";
		List<Object> param = new ArrayList<Object>();
		param.add(productName);
		try {
			DBOperation.update(sql, param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void addProduct(Product product) {
		String sql="insert into product(typeId,productName,stock,info,price,imgAddress) values(?,?,?,?,?,?)";
		List<Object> param = new ArrayList<Object>();
		param.add(product.getTypeId());
		param.add(product.getProductName());
		param.add(product.getStock());
		param.add(product.getInfo());
		param.add(product.getPrice());
		param.add(product.getImgAddress());
		try {
			DBOperation.update(sql, param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void updateProduct(Product product) {
		String sql = "update product set productName=?,stock=?,price=?,typeId=?,imgAddress=?,info=? where id=?";
		List<Object> param = new ArrayList<Object>();
		param.add(product.getProductName());
		param.add(product.getStock());
		param.add(product.getPrice());
		param.add(product.getTypeId());
		param.add(product.getImgAddress());
		param.add(product.getInfo());
		param.add(product.getId());
		
		try {
			DBOperation.update(sql, param);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	@Override
	public Integer getProductIdByProductName(String ProductName) {
		// TODO Auto-generated method stub
		String sql="select * from product where productName=?";
		List<Object> param=new ArrayList<Object>();
		param.add(ProductName);
		Integer id=null;

		ResultSet rs=DBOperation.query(sql, param);
		try {
			while(rs.next())
			{
				id=rs.getInt("id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return id;
	}
	
}
